
import java.sql.*;
import java.util.ArrayList;
import javax.swing.JOptionPane;



public class Student {
    final static int STATUS_REGULAR = 0;
    final static int STATUS_NEW = 1;
    final static int STATUS_MEDICAL =2;
    final static int STATUS_OFFCAMPUS = 3;
    
    final static int GENDER_MALE = 0;
    final static int GENDER_FEMALE = 1;
    final static int GENDER_TRANSGENDER = 2;

    
    
    public int studentID;
    public String loginName;
    private String password;
    public String firstName;
    public String lastName;
    public int gender;
    public int gradClass;
    public int status;
    public int lotteryNumber;
    
    private Connection connect;
    
    
    public Student(int studentID, String loginName, String password, String firstName, 
            String lastName, int gender, int gradClass) {
        this.studentID = studentID;
        this.loginName = loginName;
        this.password = password;
        this.firstName = firstName;
        this.lastName = lastName;
        this.gender = gender;
        this.gradClass = gradClass;
        status = STATUS_REGULAR;
        
        
    }
     
    
    
    public Student(int studentID, Connection connect){
        this.connect = connect;
        this.studentID = studentID;
        loadFromDB();
    }
    
    
    
    
    //Insert new entry to db
    public boolean addToDB(Connection connect){
        try {
            String addQuery = "INSERT INTO DORMS.student (studentID, loginName, password, firstName,"
                    + "lastName,gender,gradClass) VALUES (?,?,?,?,?,?,?)";
            PreparedStatement addStmt = connect.prepareStatement(addQuery);
            addStmt.setInt(1, studentID);
            addStmt.setString(2, loginName);
            addStmt.setString(3, password);
            addStmt.setString(4, firstName);
            addStmt.setString(5, lastName);
            addStmt.setInt(6, gender);
            addStmt.setInt(7, gradClass);
            addStmt.execute();
            Statement stmt = connect.createStatement();
            ResultSet result = stmt.executeQuery("SELECT last_insert_id() from DORMS.student");
            if (result.next())
                studentID = result.getInt("last_insert_id()");

            return true;
        }catch (Exception ex){
            JOptionPane.showMessageDialog(null,
                    "Unable to add to database. Please check for duplicates");
        }
        return false;

    }
    
    
    public boolean update(){
        try {
            String updateQuery = "UPDATE student SET firstName = ?,"
                    + "lastName = ?, gender = ?, gradClass = ?, status = ? "
                    + "WHERE studentID = ?";
            PreparedStatement updateStmt = connect.prepareStatement(updateQuery);
            updateStmt.setString(1, firstName);
            updateStmt.setString(2, lastName);
            updateStmt.setInt(3, gender);
            updateStmt.setInt(4, gradClass);
            updateStmt.setInt(5, status);
            updateStmt.setInt(6, studentID);
            int succeed = updateStmt.executeUpdate();
            if (succeed != 0)
                return true;
            else {
                JOptionPane.showMessageDialog(null,
                    "Unable to update to database");
                return false;
                
            }
        }catch (Exception ex){
            JOptionPane.showMessageDialog(null,
                    "Unable to update to database");
             
        }
            return false;
    }
    
    
    public boolean delete(){
        try {
            String deleteQuery = "DELETE FROM student WHERE studentID = ?";
            PreparedStatement updateStmt = connect.prepareStatement(deleteQuery);
            updateStmt.setInt(1, studentID);
            updateStmt.executeUpdate();
            return true;
            
                
        }catch (Exception ex){
            JOptionPane.showMessageDialog(null,
                    "Unable to delete from database");
             
        }
        return false;
        
        
    }
    
    
    
    
    /*
     * Get the rooms this student is assigned to for a particular year
     */
    
    
    public int getRoomByYear(int year){
        
        try{
            String getRoomQuery = "SELECT roomID FROM assignment "
                    + "where studentID = ? AND year = ?";
            PreparedStatement getRoomStmt = connect.prepareStatement(getRoomQuery);
            getRoomStmt.setInt(1, studentID);
            getRoomStmt.setInt(2, year);
            ResultSet roomResult = getRoomStmt.executeQuery();
            if(roomResult.next())
                return roomResult.getInt("roomID");
            
        } catch(Exception ex){
            JOptionPane.showMessageDialog(null,
                    "Unable to get room assignment.");
            
        }
        
        return -1;
        
        
    }
    
    
    
    /*
     * Get a list of surveys that this student filled out
     */
    
    public Survey getSurvey(){
        int surveyID;
        try{
            String getSurveysQuery = "SELECT studentID FROM survey where studentID = ?";
            PreparedStatement getSurveyStmt = connect.prepareStatement(getSurveysQuery);
            getSurveyStmt.setInt(1, studentID);
            ResultSet surveyResult = getSurveyStmt.executeQuery();
            if(surveyResult.next()){
                surveyID = surveyResult.getInt("studentID");
                Survey survey = new Survey(surveyID,connect);
                return survey;
            }
            
        } catch(Exception ex){
            JOptionPane.showMessageDialog(null, "Unable to get the survey.");
            
        }
        return null;
    }
    
    
    
    /*
     * Get a list of requests that this student submitted
     */
    
    public int[] getRequests(){
        ArrayList<Integer> requestsList = new ArrayList<Integer>();
        try{
            String getRequestsQuery = "SELECT requestID FROM request where studentID = ?";
            PreparedStatement getRequestsStmt = connect.prepareStatement(getRequestsQuery);
            getRequestsStmt.setInt(1, studentID);
            ResultSet requestResult = getRequestsStmt.executeQuery();
            requestResult.getRow();
            while(requestResult.next())
                requestsList.add(requestResult.getInt("requestID"));
            
        } catch(Exception ex){
            JOptionPane.showMessageDialog(null, "Unable to get the requests.");
            
        }
        
        int[] requests = new int[requestsList.size()];
        for(int i = 0; i < requestsList.size(); i++){
            requests[i] = requestsList.get(i);
        }
        
        return requests;
        
    }
    
    
    public String toString(){
        String toString = firstName + ' ' + lastName;
        return toString;
    }
            
            
    
    public ArrayList<String> getRegistrationsInYear(){
        ArrayList<String> registrations = new ArrayList<String>();
        
        try{
            String query = "SELECT studentID,year FROM registration WHERE studentID = ?";
            PreparedStatement getStmt = connect.prepareStatement(query);
            getStmt.setInt(1, studentID);
            ResultSet result = getStmt.executeQuery();
            while(result.next())
                registrations.add(String.valueOf(result.getInt("year")));
            
        } catch(Exception ex){
            JOptionPane.showMessageDialog(null, "Unable to get registrations.");
            
        }
        
        return registrations;
    }
    
    
    
    //Load object with info from db
    private void loadFromDB(){
        try {
            String loadQuery = "select * from student where studentID = ?";
            PreparedStatement loadStmt = connect.prepareStatement(loadQuery);
            loadStmt.setInt(1, studentID);
            ResultSet studentInfo = loadStmt.executeQuery();
            if(studentInfo.next()){
                loginName = studentInfo.getString("loginName");
                firstName = studentInfo.getString("firstName");
                lastName = studentInfo.getString("lastName");
                gender = studentInfo.getInt("gender");
                gradClass = studentInfo.getInt("gradClass");
                status = studentInfo.getInt("status");
                lotteryNumber = studentInfo.getInt("lotteryNumber");
            }
            
            
        } catch (Exception ex){
            JOptionPane.showMessageDialog(null,
                    "Unable to load student from database.");
            
        }
        
    }
    
    
}